from openpyxl import load_workbook
from datetime import datetime, timedelta
import os

# 定义昨天的日期字符串格式
yesterday_str = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")

# 设定性能数据文件和日志数据文件的路径
performance_data_path = r'E:\pycharm-project\nvr-demo\nvr_test\system_usage.xlsx'  # 路径需要根据实际情况进行修改
log_data_path = r'E:\pycharm-project\nvr-demo\nvr_test\log_check_report.xlsx'  # 路径需要根据实际情况进行修改

# 确保文件存在
if not os.path.exists(performance_data_path) or not os.path.exists(log_data_path):
    print("One or more files do not exist.")
    exit()

# 加载性能数据工作簿
performance_workbook = load_workbook(performance_data_path)

# 初始化存储性能问题的字典
performance_issues = {}

# 分析每个工作表的性能数据
for sheet_name in performance_workbook.sheetnames:
    if yesterday_str in sheet_name:
        performance_sheet = performance_workbook[sheet_name]
        cpu_issues = []
        mem_issues = []
        for row in performance_sheet.iter_rows(min_row=2, values_only=True):
            time, cpu, mem = row
            if cpu and cpu > 90:
                cpu_issues.append(f"{time} - {cpu}%")
            if mem and mem > 95:
                mem_issues.append(f"{time} - {mem}%")

        # 从工作表名称中获取型号名称
        model_name = sheet_name.split('_')[0]
        performance_issues[model_name] = {
            'CPU': cpu_issues,
            'MEM': mem_issues
        }

# 加载日志数据工作簿
log_workbook = load_workbook(log_data_path)

# 确保有一个以昨天日期命名的工作表，否则创建它
if yesterday_str not in log_workbook.sheetnames:
    log_sheet = log_workbook.create_sheet(yesterday_str)
else:
    log_sheet = log_workbook[yesterday_str]

# 获取该工作表的最大行数
max_row = log_sheet.max_row

# 记录性能分析结果
for model_name, issues in performance_issues.items():
    log_sheet.append([f"Model: {model_name}"])
    log_sheet.append(["CPU Usage over 90%"])
    if issues['CPU']:
        for issue in issues['CPU']:
            log_sheet.append([issue])
    else:
        log_sheet.append(["No issues"])

    log_sheet.append(["Memory Usage over 95%"])
    if issues['MEM']:
        for issue in issues['MEM']:
            log_sheet.append([issue])
    else:
        log_sheet.append(["No issues"])

# 保存日志工作簿
log_workbook.save(log_data_path)

print("Performance analysis completed and results are recorded in the log check report workbook.")
